package com.jmda.platform.database.imp;

import com.jmda.platform.database.DBCell;
import com.jmda.platform.database.DataBaseService;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.*;
import java.util.List;
import java.util.Map;

@Service("DataBaseService")
public class DataBaseServiceImp implements DataBaseService {
    @Resource(name = "dataSource")
    ComboPooledDataSource dsm = null;
    private static String supply = null;

    //从连接池获得一个连接
    @Override
    public Connection getConnection() throws SQLException {
        Connection conn = null;
        conn = dsm.getConnection();
        if (conn == null)
            throw new SQLException("数据库连接异常！");
        return conn;
    }

    //使用dbutils执行update操作
    @Override
    public int update(Connection conn, String sql, Object[] params) throws SQLException {
        // TODO Auto-generated method stub
        QueryRunner qr = new QueryRunner();
        return qr.update(conn, sql, params);
    }

    //使用dbutils执行update操作
    @Override
    public int update(String sql, Object[] params) throws SQLException {
        int r = -1;
        Connection conn = null;
        try {
            conn = getConnection();
            r = update(conn, sql, params);
        } finally {
            close(conn);
        }
        return r;

    }

    //使用dbutils执行查询操作,返回结果为指定的类型
    @Override
    public <T> T query(Connection conn, String sql, Class<T> type, Object[] params) throws SQLException {
        // TODO Auto-generated method stub
        QueryRunner qr = new QueryRunner();
        T obj = qr.query(conn, sql, new BeanHandler<T>(type), params);
        return obj;
    }

    //使用dbutils执行查询操作,返回结果为指定的类型
    @Override
    public <T> T query(String sql, Class<T> type, Object[] params) throws SQLException {
        // TODO Auto-generated method stub
        Connection conn = null;
        T obj = null;
        try {
            conn = getConnection();
            obj = query(conn, sql, type, params);
        } finally {
            close(conn);
        }
        return obj;
    }

    //使用dbutils执行查询操作,返回结果为map
    public Map<String, Object> query(String sql, Object[] params) throws SQLException {
        // TODO Auto-generated method stub
        Connection conn = null;
        Map<String, Object> map = null;
        try {
            conn = getConnection();
            map = query(conn, sql, params);
        } finally {
            close(conn);
        }
        return map;
    }

    //使用dbutils执行查询操作,返回结果为map
    @Override
    public Map<String, Object> query(Connection conn, String sql, Object[] params) throws SQLException {
        // TODO Auto-generated method stub
        QueryRunner qr = new QueryRunner();
        Map<String, Object> map = qr.query(conn, sql, new MapHandler(), params);
        // ORACLE 真实烦 还搞了一个自己的TIMESTAMP
        if (map != null) {
            for (Map.Entry<String, Object> m : map.entrySet()) {
                Object value = m.getValue();
                if (value instanceof Clob) {
                    StringBuffer content = new StringBuffer();
                    Clob clob = (Clob) value;
                    if (clob != null) {
                        Reader is = clob.getCharacterStream();
                        BufferedReader br = new BufferedReader(is);
                        String s;
                        try {
                            s = br.readLine();
                            while (s != null) {
                                content.append(s);
                                s = br.readLine();
                            }
                            m.setValue(content.toString());
                        } catch (IOException e) {
                        }
                    }
                }
            }
        }
        return map;
    }

    //使用dbutils执行单条查询操作
    @Override
    public DBCell getValue(String sql, String columnName, Object[] params) throws SQLException {
        // TODO Auto-generated method stub
        Connection conn = null;
        try {
            conn = getConnection();
            return getValue(conn, sql, columnName, params);
        } finally {
            close(conn);
        }
    }

    //使用dbutils执行单条查询操作
    @Override
    public DBCell getValue(Connection conn, String sql, String columnName, Object[] params) throws SQLException {
        QueryRunner qr = new QueryRunner();
        Map<String, Object> map = null;
        map = qr.query(conn, sql, new MapHandler(), params);
        Object obj = null;
        if (map != null) {
            obj = map.get(columnName);
        }
        DBCell cell = new DBCell();
        cell.setObj(obj);
        return cell;
    }

    //使用dbutils执行查询操作,返回结果为指定类型的list
    @Override
    public <T> List<T> queryList(Connection conn, String sql, Class<T> type, Object[] params) throws SQLException {
        // TODO Auto-generated method stub
        QueryRunner qr = new QueryRunner();
        List<T> obj = qr.query(conn, sql, new BeanListHandler<T>(type), params);
        return obj;
    }

    //使用dbutils执行查询操作,返回结果为指定类型的list
    @Override
    public <T> List<T> queryList(String sql, Class<T> type, Object[] params) throws SQLException {
        Connection conn = null;
        List<T> obj = null;
        try {
            conn = getConnection();
            obj = queryList(conn, sql, type, params);
        } finally {
            close(conn);
        }
        return obj;
    }

    //使用dbutils执行查询操作,返回结果为list<map>
    public List<Map<String, Object>> queryList(String sql, Object[] params) throws SQLException {
        Connection conn = null;
        List<Map<String, Object>> obj = null;
        try {
            conn = getConnection();
            obj = queryList(conn, sql, params);
        } finally {
            close(conn);
        }
        return obj;
    }

    //使用dbutils执行查询操作,返回结果为list<map>
    @Override
    public List<Map<String, Object>> queryList(Connection conn, String sql, Object[] params) throws SQLException {
        // TODO Auto-generated method stub
        QueryRunner qr = new QueryRunner();
        List<Map<String, Object>> obj = null;
        obj = qr.query(conn, sql, new MapListHandler(), params);
        if (obj != null && getSupply().equals(DataBaseService.SUPPLY_ORACLE)) {
            for (Map<String, Object> map : obj) {
                for (Map.Entry<String, Object> m : map.entrySet()) {
                    Object value = m.getValue();
                    if (value instanceof Clob) {
                        StringBuffer content = new StringBuffer();
                        Clob clob = (Clob) value;
                        if (clob != null) {
                            Reader is = clob.getCharacterStream();
                            BufferedReader br = new BufferedReader(is);
                            String s;
                            try {
                                s = br.readLine();
                                while (s != null) {
                                    content.append(s);
                                    s = br.readLine();
                                }
                                m.setValue(content.toString());
                            } catch (IOException e) {
                            }
                        }
                    }
                }
            }
        }
        return obj;
    }

    public String convertShortDate(String date) throws SQLException {
        if (date == null || date.trim().length() == 0)
            return null;
        if (SUPPLY_ORACLE.equals(getSupply())) {
            return "to_date('" + date + "','YYYY-mm-dd')";
        } else if (SUPPLY_MYSQL.equals(getSupply())) {
            if (date.indexOf(" ") > 0)
                date = date.substring(0, date.indexOf(" "));
            return "'" + date + "'";
        } else if (SUPPLY_DB2.equals(getSupply())) {
            if (date.indexOf(" ") > 0)
                date = date.substring(0, date.indexOf(" "));
            return "'" + date + "'";
        } else {
            return "'" + date + "'";
        }
    }

    public String convertLongDate(String date) throws SQLException {
        if (date == null || date.trim().length() == 0)
            return null;
        if (SUPPLY_ORACLE.equals(getSupply())) {
            return "to_date('" + date + "','YYYY-mm-dd hh24:mi:ss')";
        } else if (SUPPLY_MYSQL.equals(getSupply())) {
            return "'" + date + "'";
        } else if (SUPPLY_DB2.equals(getSupply())) {
            return "'" + date + "'";
        } else {
            return "'" + date + "'";
        }
    }

    public String getDateDefaultValue() throws SQLException {
        if (getSupply().equals("oracle")) {
            return " sysdate ";
        } else if (getSupply().equals("mysql")) {
            return " now() ";
        } else if (getSupply().equals("sqlserver")) {
            return " getdate() ";
        } else {
            return null;
        }
    }

    /**
     * 获得数据库类型，可能的值:SUPPLY_SQLSERVER,SUPPLY_DB2,SUPPLY_MYSQL,SUPPLY_ORACLE
     *
     * @return
     * @throws SQLException
     */
    public String getSupply() throws SQLException {
        if (supply == null) {
            Connection conn = null;
            try {
                conn = getConnection();
                supply = conn.getMetaData().getDatabaseProductName().toLowerCase();
                if ("microsoft sql server".equals(supply)) {
                    supply = SUPPLY_SQLSERVER;
                } else if (supply.indexOf(SUPPLY_DB2) != -1) {
                    supply = SUPPLY_DB2;
                } else if (supply.indexOf(SUPPLY_ORACLE) != -1) {
                    supply = SUPPLY_ORACLE;
                } else if (supply.indexOf(SUPPLY_MYSQL) != -1) {
                    supply = SUPPLY_MYSQL;
                }
            } finally {
                close(conn);
            }
        }
        return supply;
    }

    public String convertDateField(String fieldName) {
        String dateField = fieldName;
        String dbSupply = null;
        try {
            dbSupply = getSupply();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        if (dbSupply.toLowerCase().equals(SUPPLY_ORACLE)) {
            dateField = "trunc(" + fieldName + ")";
        } else if (dbSupply.toLowerCase().equals(SUPPLY_SQLSERVER)) {
            dateField = "CONVERT(CHAR(10), " + fieldName + ", 120)";
        } else if (dbSupply.toLowerCase().equals(SUPPLY_MYSQL)) {
            dateField = "DATE(" + fieldName + ")";
        }
        return dateField;
    }

    public String getViewSQL(String sql, int start, int lineNumber) throws SQLException {
        Connection conn = this.getConnection();
        try {
            return getViewSQL(conn, getSupply(), sql, start, lineNumber);
        } finally {
            close(conn);
        }
    }

    public String getViewSQL(Connection conn, String dbSupply, String statment, int start, int lineNumber) {
        /**
         * oracle数据库分页SQL语句
         */
        // 解决 分页时候 起始页 0，1 的兼容性问题 ，潜在问题，每页只显示1条记录时 存在缺陷
        start = start == 0 ? 1 : start;
        if (dbSupply.toLowerCase().equals("oracle")) {
            if (start > 1) {
                start = start + 1;
            }
            // oracle需要判断首页的特殊情况，修正最后一页少一条记录的问题
            return "select * from (select rownum r,t1.* from (" + statment + ") t1 where rownum<=" + (start - 1 + lineNumber) + ") t2 where t2.r>=" + start;
        } else if (dbSupply.toLowerCase().equals("sqlserver")) {
            /**
             * sqlserver数据库分页SQL语句
             */
            String statmentTmp = statment;
            String orderby = "";
            if (statment.toUpperCase().indexOf("ORDER BY") > 0) {
                statmentTmp = statment.substring(0, statment.toUpperCase().indexOf("ORDER BY")).trim();
                orderby = statment.substring(statment.toUpperCase().indexOf("ORDER BY"));
            }
            if (orderby.equals("")) {
                orderby = getSqlserverOrderBy(conn, statment);
            }

            return "select * from ( select ROW_NUMBER() OVER(" + orderby + ") as rownum ," + statmentTmp.substring(statmentTmp.toLowerCase().indexOf("select") + 6) + ") as t where rownum between " + (start > 1 ? start + 1 : start) + " and "
                    + (start == 1 ? lineNumber : start + lineNumber);
        } else if (dbSupply.toLowerCase().equals("mysql")) {
            /**
             * mysql数据库分页SQL语句
             */
            return "select * from (" + statment + ") tmpTab limit " + (start > 0 ? start - 1 : start) + "," + lineNumber;
        }
        return statment;
    }

    private String getSqlserverOrderBy(Connection conn, String statment) {
        String column = "";
        Statement st = null;
        ResultSet rs = null;
        try {
            st = conn.createStatement();
            rs = st.executeQuery(statment);
            ResultSetMetaData rsmd = rs.getMetaData();
            column = rsmd.getColumnName(1);
        } catch (SQLException e) {
            e.printStackTrace(System.err);
        } finally {
            try {
                st.close();
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        String newStatment = statment.toUpperCase();
        String tableName = "";
        if ((newStatment.indexOf("FROM") > 0) && (newStatment.indexOf("WHERE") > 0)) {
            String tableList = newStatment.substring(newStatment.indexOf("FROM") + 4, newStatment.indexOf("WHERE")).trim();
            if (tableList.indexOf(",") > -1) {
                tableName = tableList.trim().split(",")[0];
                if (tableName.split("\\s+").length > 1) {
                    tableName = tableName.split("\\s+")[0];
                }
            }
        }

        return "ORDER BY " + (tableName.equals("") ? column : (tableName + "." + column));
    }

    @Override
    public void close(Connection conn) {
        // TODO Auto-generated method stub
        try {
            conn.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}
